﻿CREATE PROCEDURE [dbo].[Articles_UpdateArticleStatus]
	 @RangeReviewId INT
AS

BEGIN TRAN

declare @startDate DATE = (select FirstMonth from RangeReviews where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews where Id = @RangeReviewId)
declare @startDatePY DATE = 
	(
		select CASE WHEN HasPriorYear = 1 THEN DATEADD(Month, -12, FirstMonth) ELSE FirstMonth END
		from RangeReviews where Id = @RangeReviewId
	)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @limitDate DATE = DATEADD(Month, -2, @endDate)

declare @totalSales int = (select COUNT(*) 
						  from ArticleSales s
						  inner join Articles ar on ar.Id = s.ArticleId
						  where ar.RangeReviewId = @RangeReviewId)

IF @totalSales > 0

Begin

	--Actualizo los New (Estan en el article master no tienen ventas en ninguno de los 2 periodos y su fecha de creacion es reciente)
	Update Articles 
	Set IsDeletedRangeReview = 1, IsNew = 1, IsDelisted = 0
	from Articles  ar
	where ar.CreatedDate >= @limitDate
	and ar.Source = 'ArticleMaster' and ar.RangeReviewId = @RangeReviewId
	and ar.Id not in 
	(
		select distinct ArticleId 
		from ArticleSales
		Where (Date BETWEEN @startDatePY AND DATEADD(Month, @totalMonths - 1, @startDatePY) OR Date Between @startDate AND @endDate)
		AND StoreId in (Select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0)
		group by ArticleId
		having SUM(Sales) > 0
	)
	
	IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRAN
		RAISERROR('Error Actualizando News.', 16, 1)
		RETURN
	END
	
	--Actualizo los New Truchos o CleanUp (Estan en el article master no tienen ventas en ninguno de los 2 periodos y su fecha de creacion es vieja)
	Update Articles 
	Set IsDeletedRangeReview = 1, IsNew = 0, IsDelisted = 0
	from Articles ar
	where ar.CreatedDate < @limitDate
	and ar.Source = 'ArticleMaster' and ar.RangeReviewId = @RangeReviewId
	and ar.Id not in 
	(
		select distinct ArticleId 
		from ArticleSales
		Where (Date BETWEEN @startDatePY AND DATEADD(Month, @totalMonths - 1, @startDatePY) OR Date Between @startDate AND @endDate)
		AND StoreId in (Select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0)
		group by ArticleId
		having SUM(Sales) > 0
	)
	
	IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRAN
		RAISERROR('Error Actualizando CleanUp.', 16, 1)
		RETURN
	END
	
	--Actualizo los Delisted (Estan en el article master y tienen ventas en el PY y no en el CY)
	Update Articles 
	Set IsDeletedRangeReview = 1, IsDelisted = 1, IsNew = 0
	from Articles ar
	where ar.Source = 'ArticleMaster' and ar.RangeReviewId = @RangeReviewId
	and ar.Id not in 
	(
		select distinct ArticleId 
		from ArticleSales
		Where (Date Between @startDate AND @endDate)
		AND StoreId in (Select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0)
		group by ArticleId
		having SUM(Sales) > 0
	)
	and ar.Id in
	(
		select distinct ArticleId 
		from ArticleSales
		Where (Date BETWEEN @startDatePY AND DATEADD(Month, @totalMonths - 1, @startDatePY))
		AND StoreId in (Select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0)
		group by ArticleId
		having SUM(Sales) > 0
	)
	
	IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRAN
		RAISERROR('Error Actualizando Delisted.', 16, 1)
		RETURN
	END
	
	--Si el Source es distinto a ArticleMaster entonces es delistado
	Update Articles 
	Set IsDeletedRangeReview = 1, IsDelisted = 1, IsNew = 0
	from Articles ar
	where ar.Source != 'ArticleMaster' and ar.RangeReviewId = @RangeReviewId and (ar.IsDeletedRangeReview = 0 OR ar.IsDelisted = 0)
	
	IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRAN
		RAISERROR('Error Actualizando Delisted.', 16, 1)
		RETURN
	END
	
	--Si es importación de ventas parcial entonces 
	--Activo los que estan en el ArticleMaster y tienen ventas currentyear
	Update Articles 
	Set IsDeletedRangeReview = 0, IsNew = 0, IsDelisted = 0
	where Source = 'ArticleMaster' and RangeReviewId = @RangeReviewId
	and Id in 
	(
		select distinct ArticleId 
		from ArticleSales
		Where (Date Between @startDate AND @endDate)
		AND StoreId in (Select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0)
		group by ArticleId
		having SUM(Sales) > 0
	)
	
	IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRAN
		RAISERROR('Error Actualizando Active.', 16, 1)
		RETURN
	END
	
	--Por las dudas actualizo el status IsDeletedRangeReview
	Update Articles
	Set IsDeletedRangeReview = 1
	Where IsDeletedRangeReview = 0 AND (IsNew = 1 OR IsDelisted = 1) and RangeReviewId = @RangeReviewId
	
	--Por las dudas actualizo el status IsDeletedRangeReview cuando IsDeletedPerformance = 1
	Update Articles
	Set IsDeletedRangeReview = 1
	Where IsDeletedRangeReview = 0 AND IsDeletedPerformance = 1 and RangeReviewId = @RangeReviewId

End

--Marco los COMP y HAWK como DeletedRangeReview y DeletedPerformance
Update Articles
Set IsDeletedRangeReview = 1,
IsDeletedPerformance = 1
Where Type IN ('COMP', 'HAWK') and RangeReviewId = @RangeReviewId

	IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRAN
		RAISERROR('Error Actualizando COMP.', 16, 1)
		RETURN
	END

COMMIT TRAN